#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
@file: field.py
@time: 2024-06-22 18:46
@desc:
"""
import json
import traceback
from io import BytesIO
from urllib.parse import urlencode

from flask import request, jsonify, Blueprint, send_file, url_for
from sqlalchemy import desc

from project_model import FieldStack, Contact, Image
from tools import log_operations, token_required, get_boxs_df2
from sqlalchemy.orm import joinedload
from global_scope import MAX_PAGE_SIZE, get_db
import pandas as pd
from tools import get_field_stack_df, get_contacts_df, excel_to_field_stacks
from tools import get_images_df, excel_to_images, excel_to_contacts
from global_scope import contacts_sheet_name, field_stacks_sheet_name, images_sheet_name
from common_tools import apply_formatting
from tools import get_configuration

db = get_db()

field_stack_bp = Blueprint('field_stack', __name__, url_prefix='/field_stack')


@field_stack_bp.route('', methods=['POST'])
@log_operations
@token_required
def add_field_stack():
    data = request.json
    name = data.get('name')
    address = data.get('address')
    phone = data.get('phone')
    website = data.get('website')
    remark = data.get('remark')

    if not name or not address:
        return jsonify({"code": 1, "message": "Name and address are required"}), 400

    field_stack = FieldStack.query.filter_by(name=name, address=address).first()
    if not field_stack:
        field_stack = FieldStack(
            name=name, address=address, phone=phone, website=website, remark=remark)

    # Handle contacts field
    if 'contacts' in data:
        contact_ids = data['contacts']
        contacts = Contact.query.filter(Contact.id.in_(contact_ids)).all()
        field_stack.contacts = contacts

    # Handle images field
    if 'images' in data:
        image_ids = data['images']
        images = Image.query.filter(Image.id.in_(image_ids)).all()
        field_stack.images = images

    db.session.add(field_stack)
    db.session.commit()

    # Prepare response data
    field_stack_data = {
        "id": field_stack.id,
        "name": field_stack.name,
        "phone": field_stack.phone,
        "address": field_stack.address,
        "website": field_stack.website,
        "remark": field_stack.remark,
        "contacts": [{"id": c.id, "name": c.name, "phone": c.phone} for c in field_stack.contacts],
        "images": [{"id": i.id, "imageUrl": i.imageUrl} for i in field_stack.images]
    }

    return jsonify({
        "code": 0,
        "message": "Field stack added successfully",
        "data": field_stack_data}), 201


@field_stack_bp.route('/<int:field_id>', methods=['GET'])
@log_operations
@token_required
def get_field_stack(field_id):
    field_stack = FieldStack.query.get(field_id)
    if not field_stack:
        return jsonify({"code": 1, "message": "Field stack not found"}), 404

    return jsonify({"code": 0, "message": "OK", "data": field_stack.to_dict()}), 200


@field_stack_bp.route('', methods=['GET'])
@log_operations
@token_required
def get_all_field_stacks():
    name = request.args.get('name')
    phone = request.args.get('phone')
    address = request.args.get('address')
    contactor = request.args.get('contactor')
    contactor_phone = request.args.get('contactor_phone')
    remark = request.args.get('remark')

    # Pagination parameters
    page_no = request.args.get('pageNo', default=1, type=int)
    page_size = request.args.get('pageSize', default=MAX_PAGE_SIZE, type=int)

    query = db.session.query(FieldStack).options(joinedload(FieldStack.contacts), joinedload(FieldStack.images))

    if name:
        query = query.filter(FieldStack.name.contains(name))
    if phone:
        query = query.filter(FieldStack.phone.contains(phone))
    if address:
        query = query.filter(FieldStack.address.contains(address))
    if contactor:
        query = query.join(FieldStack.contacts).filter(Contact.name.contains(contactor))
    if contactor_phone:
        query = query.join(FieldStack.contacts).filter(Contact.phone.contains(contactor_phone))
    if remark:
        query = query.filter(FieldStack.remark.contains(remark))

    # Add sorting and pagination logic
    query = query.order_by(desc(FieldStack.id))
    total_count = query.count()
    query = query.offset((page_no - 1) * page_size).limit(page_size)
    field_stacks = query.all()

    data = []
    for fs in field_stacks:
        contacts = [{'id': c.id, 'name': c.name, 'phone': c.phone} for c in fs.contacts]
        images = [{'id': i.id, 'imageUrl': i.imageUrl} for i in fs.images]
        data.append({
            "id": fs.id,
            "name": fs.name,
            "phone": fs.phone,
            "address": fs.address,
            "website": fs.website,
            "remark": fs.remark,
            "contacts": contacts,
            "images": images,
            "value": f"{fs.name} | {fs.address}",
            "createTime": fs.createTime.isoformat(),
            "updateTime": fs.updateTime.isoformat()
        })
    response_data = {
        "pageData": data,
        "total": total_count,
    }
    origin_url = url_for('field_stack.get_all_field_stacks', _external=True)
    if request.args:
        origin_url += '?' + urlencode(request.args)

    return jsonify({
        "code": 0,
        "message": "OK",
        "data": response_data,
        "originUrl": origin_url
    }), 200


@field_stack_bp.route('/<int:field_id>', methods=['PATCH'])
@log_operations
@token_required
def update_field_stack(field_id):
    data = request.json
    field_stack = FieldStack.query.get(field_id)
    if not field_stack:
        return jsonify({"code": 1, "message": "Field stack not found"}), 404

    # Update FieldStack attributes
    if 'name' in data:
        field_stack.name = data['name']
    if 'phone' in data:
        field_stack.phone = data['phone']
    if 'address' in data:
        field_stack.address = data['address']
    if 'website' in data:
        field_stack.website = data['website']
    if 'remark' in data:
        field_stack.remark = data['remark']

    # Update contacts
    if 'contacts' in data:
        contact_ids = list()
        for c in data['contacts']:
            if c and isinstance(c, dict) and 'id' in c:
                contact_ids.append(c['id'])
            elif c and isinstance(c, int):
                contact_ids.append(c)
            elif c and isinstance(c, str) and c.isdigit():
                contact_ids.append(int(c))
        contacts = Contact.query.filter(Contact.id.in_(contact_ids)).all()
        field_stack.contacts = contacts

    # Update images
    if 'images' in data:
        image_ids = [c.get('id') for c in data['images'] if c.get('id')]
        images = Image.query.filter(Image.id.in_(image_ids)).all()
        field_stack.images = images

    db.session.commit()

    field_stack_data = {
        "id": field_stack.id,
        "name": field_stack.name,
        "phone": field_stack.phone,
        "address": field_stack.address,
        "website": field_stack.website,
        "remark": field_stack.remark,
        "contacts": [{"id": c.id, "name": c.name, "phone": c.phone} for c in field_stack.contacts],
        "images": [{"id": i.id, "imageUrl": i.imageUrl} for i in field_stack.images]
    }

    return jsonify({"code": 0, "message": "Field stack updated successfully", "data": field_stack_data}), 200


@field_stack_bp.route('/<int:field_id>', methods=['DELETE'])
@log_operations
@token_required
def delete_field_stack(field_id):
    field_stack = FieldStack.query.get(field_id)
    if not field_stack:
        return jsonify({"code": 1, "message": "Field stack not found"}), 404

    db.session.delete(field_stack)
    db.session.commit()
    return jsonify({"code": 0, "message": "Field stack deleted successfully"}), 200


def field_stack_to_dict(field_stack):
    return {
        "id": field_stack.id,
        "name": field_stack.name,
        "phone": field_stack.phone,
        "address": field_stack.address,
        "website": field_stack.website,
        "remark": field_stack.remark,
        "contacts": [{"id": c.id, "name": c.name, "phone": c.phone} for c in field_stack.contacts],
        "images": [{"id": i.id, "imageUrl": i.imageUrl} for i in field_stack.images]
    }


FieldStack.to_dict = field_stack_to_dict


@field_stack_bp.route('/upload', methods=['POST'])
@log_operations
@token_required
def upload_field_stack():
    if 'file' not in request.files:
        return jsonify({"code": 1, "message": "No file part"}), 400

    file = request.files['file']
    if file.filename == '':
        return jsonify({"code": 1, "message": "No selected file"}), 400

    try:
        excel_to_images(file, request)
        excel_to_contacts(file, request)
        new_records, updated_records = excel_to_field_stacks(file, request)

        return jsonify({
            "code": 0,
            "message": f"Field stack uploaded successfully: {new_records} added, {updated_records} updated"
        }), 201
    except Exception as e:
        tb = traceback.format_exc()
        request.trace_logger.error(f"Exception occurred: {str(e)}\n{tb}")
        return jsonify({"code": 1, "message": str(e)}), 500


@field_stack_bp.route('/download', methods=['GET'])
@log_operations
@token_required
def download_field_stack():
    df = get_field_stack_df()

    output = BytesIO()
    with pd.ExcelWriter(output, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name=field_stacks_sheet_name)
        df_contacts = get_contacts_df()
        df_contacts.to_excel(writer, index=False, sheet_name=contacts_sheet_name)
        df_images = get_images_df()
        df_images.to_excel(writer, index=False, sheet_name=images_sheet_name)

        # Apply formatting using the utility function
        apply_formatting(writer.sheets[field_stacks_sheet_name], df)
        apply_formatting(writer.sheets[contacts_sheet_name], df_contacts)
        apply_formatting(writer.sheets[images_sheet_name], df_images)

    output.seek(0)

    return send_file(output, download_name="FieldStack.xlsx", as_attachment=True)


def format_id_key(s, p):
    return f"{p}/{s}"


def transform_data(df):
    ids = list()
    labels = list()
    parents = list()
    values = list()

    for _, row in df.iterrows():
        areaFieldStackName = row['areaFieldStackName']
        areaFieldStackName_id = areaFieldStackName
        location = row['location']
        location_id = format_id_key(location, areaFieldStackName_id)
        areaName = row['areaName']
        areaName_id = format_id_key(areaName, location_id)
        contactName = row['contactName']
        contactName_id = format_id_key(contactName, areaName_id)
        boxTypeName = row['boxTypeName']
        boxTypeName_id = format_id_key(boxTypeName, contactName_id)
        count = row["count"]  # 每行代表一个箱子
        max_capacity = int(row["max_capacity"])

        if areaFieldStackName_id not in ids:
            ids.append(areaFieldStackName_id)
            labels.append(areaFieldStackName)
            parents.append("")
            values.append(0)
        if location == "场外":
            index = ids.index(areaFieldStackName_id)
            values[index] += count

        if location_id not in ids:
            ids.append(location_id)
            labels.append(location)
            parents.append(areaFieldStackName_id)
            values.append(0)
        if location == "场外":
            index = ids.index(location_id)
            values[index] += count

        if areaName_id not in ids:
            ids.append(areaName_id)
            num = count
            if location == "场内":
                num = max_capacity
                # 累加相同areaFieldStackName的count
                index = ids.index(areaFieldStackName_id)
                values[index] += max_capacity
                # 累加相同location的count
                index = ids.index(location_id)
                values[index] += max_capacity
                labels.append(f"{areaName}/容量")
            else:
                labels.append(areaName)
            values.append(num)
            parents.append(location_id)
            if location == "场内":
                ids.append(areaName_id + '/已用')
                labels.append(areaName + '/已用')
                parents.append(areaName_id)
                values.append(count)
        elif location == "场外":
            # 累加相同areaFieldStackName的count
            index = ids.index(areaName_id)
            values[index] += count
        elif location == "场内":
            # 累加相同areaFieldStackName的count
            index = ids.index(areaName_id + '/已用')
            values[index] += count

        if contactName_id not in ids:
            ids.append(contactName_id)
            labels.append(contactName)
            if location == "场外":
                parents.append(areaName_id)
            else:
                parents.append(areaName_id + '/已用')
            values.append(count)
        else:
            # 累加相同contactName的count
            index = ids.index(contactName_id)
            values[index] += count

        if boxTypeName_id not in ids:
            ids.append(boxTypeName_id)
            labels.append(boxTypeName)
            parents.append(contactName_id)
            values.append(count)
        else:
            # 累加相同boxTypeName的count
            index = ids.index(boxTypeName_id)
            values[index] += count

    return [{
        'ids': ids,
        "labels": labels,
        "parents": parents,
        "values": values,
        'type': "sunburst",
        'outsidetextfont': {'size': 20, 'color': "#377eb8"},
        'leaf': {'opacity': 0.4},
        'marker': {'line': {'width': 2}},
        "branchvalues": 'total'
    }]


@field_stack_bp.route('/infos', methods=['GET'])
@log_operations
@token_required
def get_all_field_infos():
    boxs_df = get_boxs_df2()
    # 清洗数据，填充缺失值
    boxs_df.fillna('', inplace=True)
    x, y = boxs_df.shape
    if 0 == x:
        data = json.loads(get_configuration("SunburstData", request))
        return jsonify({"code": 0, "data": data}), 200
    # 按照指定维度分组并计数
    grouped_data = boxs_df.groupby(
        ['areaFieldStackName', 'location', 'max_capacity', 'areaName', 'contactName',
         'boxTypeName']).size().reset_index(name='count')
    json_str = transform_data(grouped_data)
    return jsonify({"code": 0, "data": json_str}), 200
